# -*- coding:utf-8 -*-

"""
@author: fanqc
@file: excel_helper.py
@time: 2023/2/15 13:58
"""
import datetime
import os
import re
from os.path import isfile
from tkinter import messagebox

from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.worksheet import Worksheet

from conf import log
from exceptions import RunException


def _convert_value(value):
    """
    将单元格中数据，区分基本类型
    相似"true"/"false"(不区分大小写)转换为bool值
    长得像数字的转换为str类型
    其余（空格、空行）转换为None
    :param value: 单元格的值
    :return: 转换后的类型
    """
    value_str = str(value).lower()
    if value_str == 'true':
        return True
    elif value_str == 'false':
        return False
    elif re.match(r"^[+|-]?\d+.?\d*$", value_str):
        return str(value_str)
    elif re.match(r"^\s*$", value_str):
        return None
    else:
        return value


class ExcelHelper:
    """
    Excel 处理
    """

    @classmethod
    def convert_2_cls(cls, file_path, clazz, sheet_name=None):
        """
        转换为class，可转换多张sheet表，转换为统一clazz对象
        过滤掉为空行
        :param sheet_name:
        :param file_path:Excel文件路径
        :param clazz:结果转换为clazz对象
        :return: 对象列表的列表，结构为[[clazz(),clazz()],[clazz()]]
        """
        if not file_path.endswith(".xlsx"):
            messagebox.showinfo("文件必须为.xlsx结尾的Excel文件")
            raise RunException("文件必须为.xlsx结尾的Excel文件")
        if not isfile(file_path):
            messagebox.showinfo("文件路径 {0} 不存在".format(file_path))
            raise RunException("文件路径 {0} 不存在".format(file_path))
        work_book = load_workbook(file_path)
        result = []
        if sheet_name:
            result = cls.parse_sheet(work_book, sheet_name, clazz)
        else:
            for sheet_name in work_book.sheetnames:
                table = cls.parse_sheet(work_book, sheet_name, clazz)
                result.extend(table)

        return result

    @classmethod
    def parse_sheet(cls, work_book, sheet_name, clazz):
        ws: Worksheet = work_book[sheet_name]
        if ws.max_row > 1001:
            raise RunException("Excel文件请勿过大，控制在1000行以内")
        # 获取表头
        table_heads = []

        for title_row in ws.iter_rows(max_row=1):
            for cell in title_row:
                table_heads.append(cell.value)
        # 获取表数据
        table = []
        for row in ws.iter_rows(min_row=2):
            # 实例化对象
            instance = clazz()
            for column_index in range(len(row)):
                setattr(instance, table_heads[column_index], _convert_value(row[column_index].value))

            # 过滤空行（全部属性均为None的对象）
            is_valid = False
            for attr in instance.__dict__:
                if not attr.startswith("_") and instance.__dict__[attr] is not None:
                    is_valid = True
                    break
            if is_valid:
                table.append(instance)
        return table

    @classmethod
    def save(cls, file_path, tables, sheet_name=None):
        if not file_path.endswith(".xlsx"):
            raise RunException("文件必须为.xlsx结尾的Excel文件")
        work_book = Workbook()
        is_first = True
        for table in tables:
            if is_first:
                ws = work_book.create_sheet(title=sheet_name)
                is_first = False
            else:
                ws = work_book.create_sheet(title=sheet_name + datetime.datetime.now().strftime('%Y_%m_%d_%H_%M_%S'))
            # 添加表头
            table_heads = []
            for attr in table[0].__dict__:
                # 过滤"_"开头的属性
                if not attr.startswith("_"):
                    table_heads.append(attr)
            ws.append(table_heads)

            # 添加数据
            for row in table:
                data = []
                for head in table_heads:
                    data.append(getattr(row, head))
                ws.append(data)
        try:
            # 生成保存文件夹路径
            folder_index = max(file_path.rfind("\\"), file_path.rfind("/"))
            if folder_index != -1:
                folder_path = file_path[0:folder_index]
                if not os.path.exists(folder_path):
                    os.mkdir(folder_path)
            work_book.save(file_path)
        except Exception as e:
            messagebox.showerror("创建Excel文件失败!")
            log.error(str(e))
